﻿'Description:集件管理类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2017-5-8
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports DatabaseAccess.DBUtility
Imports System.Text
Imports System.Data.OracleClient

Public Class uLMSIssueBusiness
    Implements uLMSIssueIBusiness
    ''' <summary>
    ''' 获取集件管理批次信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Protected Function GetCollectPieceManageData(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSIssueIBusiness.GetCollectPieceManageData
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,c.workflowname||':'||c.workflowrevision WORKFLOWNAMEVERSION,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,(c.productname||'/'||c.containerno) ProductBatch, 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,''BOMID")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")

        Dim sbCondition As String = GetCondition(QueryCondition)
        sbCondition += String.Format("  AND C.FIRSTCHECKNUM=0 AND C.STEPCHECKNUM=0 AND C.BOMID IS NOT NULL AND C.ISINSTORE=0") 'update:Wangjh 2017-7-7 增加入库判段
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,c.containerno,c.workflowname,c.workflowrevision")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.BOMID")
        sb.AppendLine(" FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString

        sbCondition = String.Format("SELECT C.* FROM CONTAINERCURRENTINFO C {0}", sbCondition)

        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
    End Function
    ''' <summary>
    ''' 获取集件批次BOM信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetCollectPieceManageBOMList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueIBusiness.GetCollectPieceManageBOMList
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT S.SPECNO, PBN.PRODUCTNAME, PML.QTYREQUIRED,PML.PRODUCTMATERIALLISTITEMID,WS.WORKFLOWSTEPID," &
                          "IEM.ISSUEQTY,IEM.PRODUCTCHILDNO,E.fullname,IEM.NOTE,IEM.ISSUEERPMATERIALID,WS.WORKFLOWSTEPNAME," &
                          "IEM.BOMMATERIALLISTITEMID,IEM.COLLECTERPMATERIALID,LSQS.ISSCRAP,LSQS.LOSSQTYSUM,PML.PRODUCTID,IEM2.PRODUCTCHILDNO TOPRODUCTCHILDNO FROM PRODUCTMATERIALLISTITEM PML")
        strSql.AppendLine(" LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = PML.PRODUCTBASEID")
        strSql.AppendLine(" LEFT JOIN PRODUCT P ON P.PRODUCTID =NVL(PB.REVOFRCDID, PML.PRODUCTID)")
        strSql.AppendLine(" LEFT JOIN PRODUCTBASE PBN ON PBN.PRODUCTBASEID=P.PRODUCTBASEID")
        strSql.AppendLine(" LEFT JOIN SPECBASE SB ON SB.SPECBASEID=PML.SPECBASEID")
        strSql.AppendLine("  LEFT JOIN SPEC S ON  S.SPECID=NVL(SB.REVOFRCDID, PML.SPECID)")
        strSql.AppendFormat(" LEFT JOIN ISSUEERPMATERIAL IEM ON IEM.BOMMATERIALLISTITEMID=PML.PRODUCTMATERIALLISTITEMID AND IEM.CONTAINERID='{0}'", para("ContainerID"))
        strSql.AppendLine(" LEFT JOIN EMPLOYEE E ON IEM.EMPLOYEEID=E.EMPLOYEEID")
        strSql.AppendLine(" left join specbase sb2 on sb2.REVOFRCDID=s.specid") '工序选择默认版本情况
        strSql.AppendLine("  LEFT JOIN WORKFLOWSTEP WS ON (WS.SPECBASEID=PML.SPECBASEID AND PML.SPECID='0000000000000000') OR (WS.SPECID=PML.SPECID AND PML.SPECBASEID='0000000000000000') OR (WS.SPECBASEID=SB2.SPECBASEID AND WS.SPECID='0000000000000000')")
        strSql.AppendLine(" LEFT JOIN (SELECT SUM(IEMS.LOSSQTY) LOSSQTYSUM,COUNT(*) ISSCRAP,IssueERPMaterialID FROM IssueERPMaterialScrapInfo IEMS" &
                          " WHERE IEMS.CheckerID IS NOT NULL" &
                            " GROUP BY IssueERPMaterialID) LSQS" &
                            " ON LSQS.IssueERPMaterialID=IEM.ISSUEERPMATERIALID")
        strSql.AppendLine("  LEFT JOIN ISSUEERPMATERIAL IEM2 ON IEM2.ISSUEERPMATERIALID=IEM.TOISSUEERPMATERIALID")
        strSql.AppendFormat(" WHERE PML.BOMID = '{0}' AND WS.WORKFLOWID='{1}'", para("BomID"), para("WorkFlowID"))

        If para.ContainsKey("FactoryID") Then
            strSql.AppendFormat(" AND S.FACTORYID='{0}'", para("FactoryID"))
        End If
        If para.ContainsKey("Sequence") Then
            If para.Keys.Contains("IsCurrentStep") Then
                strSql.AppendFormat(" AND WS.SEQUENCE={0}", para("Sequence"))
            Else
                strSql.AppendFormat(" AND WS.SEQUENCE>={0}", para("Sequence"))
            End If
        End If
        strSql.AppendLine(" ORDER BY S.SPECNO,PBN.PRODUCTNAME")
        GetCollectPieceManageBOMList = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 获取ERP集件信息表数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetCollectErpMaterial(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSIssueIBusiness.GetCollectErpMaterial
        Dim sbSql As New StringBuilder
        sbSql.AppendLine("SELECT CEM.QTY,CEM.COLLECTERPMATERIALID,CEM.CREATEDATE,CEM.ISAUTOPLAN," &
                         "CEM.ISNONCONFORMING,CEM.MFGORDERNAME,CEM.ORIGINALPRODUCTNAME,CEM.PRODUCTBATCHNO,CEM.PRODUCTNAME," &
                         "CEM.UOM FROM COLLECTERPMATERIAL CEM ")
        sbSql.AppendFormat(" WHERE CEM.MFGORDERNAME='{0}' AND CEM.PRODUCTNAME='{1}' ", para("MfgOrderName"), para("ProductName"))
        sbSql.AppendLine(" ORDER BY CEM.Createdate")
        Dim result = DatabaseHelper.Query_ReturnDatTable(sbSql.ToString)
        GetCollectErpMaterial = result
    End Function

    ''' <summary>
    ''' 获取弹出框集件批次BOM信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetPoupMaterialBomData(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSIssueIBusiness.GetPoupMaterialBomData
        Dim strSql As New StringBuilder
        strSql.AppendFormat("SELECT NVL(IEM.ISSUEQTY-LSQS.LOSSQTYSUM,IEM.ISSUEQTY) NEWISSUEQTY,NVL(LSQS.LOSSQTYSUM,0) LOSSQTYSUM,CEM.QTY CollectErpQty,IEM.IssueQty,IEM.Productchildno,IEM.BomMaterialListItemID,IEM.CollectErpMaterialID,IEM.IssueErpMaterialID,NVL(IEM2.ISSUEQTY,0) AlreadyChangeQty,IEM2.PRODUCTCHILDNO TOPRODUCTCHILDNO,IEM2.ISSUEERPMATERIALID ToIssueERPMaterialID FROM ISSUEERPMATERIAL IEM" &
            " LEFT JOIN (SELECT SUM(LOSSQTY) LOSSQTYSUM, ISSUEERPMATERIALID FROM ISSUEERPMATERIALSCRAPINFO IEMS" &
            " GROUP BY ISSUEERPMATERIALID) LSQS" &
            " ON LSQS.ISSUEERPMATERIALID = IEM.ISSUEERPMATERIALID" &
            " LEFT JOIN PRODUCTMATERIALLISTITEM PML ON PML.PRODUCTMATERIALLISTITEMID=IEM.BOMMATERIALLISTITEMID" &
            " LEFT JOIN COLLECTERPMATERIAL CEM ON CEM.COLLECTERPMATERIALID=IEM.COLLECTERPMATERIALID" &
            " LEFT JOIN issueerpmaterial iem2 ON iem.TOISSUEERPMATERIALID=IEM2.ISSUEERPMATERIALID" &
            " WHERE IEM.WORKFLOWSTEPID='{0}' AND IEM.CONTAINERID='{1}' AND PML.PRODUCTID='{2}' ", para("WorkFlowStepID"), para("ContainerID"), para("ProductID"))
        If para.Keys.Contains("IsScrapInfo") Then
            strSql.AppendFormat(" AND LSQS.LOSSQTYSUM IS NOT NULL")
        End If
        strSql.AppendLine(" GROUP BY LSQS.LOSSQTYSUM,CEM.QTY,IEM.IssueQty,IEM.Productchildno,IEM.BomMaterialListItemID,IEM.CollectErpMaterialID,IEM.IssueErpMaterialID,IEM.TOISSUEERPMATERIALID,IEM2.ISSUEQTY,IEM2.PRODUCTCHILDNO,IEM2.ISSUEERPMATERIALID")

        strSql.AppendLine(" ORDER BY IEM.PRODUCTCHILDNO")

        GetPoupMaterialBomData = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 创建批次查询数据条件
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE C.FACTORYID='{0}' ", para("FactoryID"))
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE'%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:wangjh 2017-8-8
            sbCondition.AppendFormat(" AND C.mfgordername LIKE'%{0}%'", para("MfgOrderName"))
        End If
        GetCondition = sbCondition.ToString
    End Function
    ''' <summary>
    ''' 插入数据到装配信息表
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertIssueErpMaterial(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.InsertIssueErpMaterial
        Dim insertSql As String = "INSERT INTO ISSUEERPMATERIAL(BOMMATERIALLISTITEMID,COLLECTERPMATERIALID,CONTAINERID," &
                                  " CREATEDATE,EMPLOYEEID,FACTORYID,ISSUEQTY,NOTE,PRODUCTCHILDNO,WORKFLOWSTEPID,ISSUEERPMATERIALID,ToIssueERPMaterialID)"
        insertSql += "VALUES(:BOMMATERIALLISTITEMID,:COLLECTERPMATERIALID,:CONTAINERID,SYSDATE,:EMPLOYEEID,:FACTORYID,:ISSUEQTY," &
                        ":NOTE,:PRODUCTCHILDNO,:WORKFLOWSTEPID,:ISSUEERPMATERIALID,:ToIssueERPMaterialID)"

        Dim insertPara(10) As OracleParameter
        'modify tianFW 2017/10/10 
        insertPara(0) = DatabaseHelper.NewParameter(":BOMMATERIALLISTITEMID", para("ProductMaterialListItemID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":COLLECTERPMATERIALID", para("CollectErpMaterialID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":EMPLOYEEID", para("EmployeeID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":FACTORYID", para("FactoryID"), OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":ISSUEQTY", para("IssueQty"), OracleType.Number)
        insertPara(6) = DatabaseHelper.NewParameter(":NOTE", para("Note"), OracleType.VarChar)
        insertPara(7) = DatabaseHelper.NewParameter(":PRODUCTCHILDNO", para("ProductChildNO"), OracleType.VarChar)
        insertPara(8) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkFlowStepID"), OracleType.VarChar)
        If para.ContainsKey("IssueErpMaterialID") Then
            insertPara(9) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", para("IssueErpMaterialID"), OracleType.VarChar)
        Else
            insertPara(9) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", Guid.NewGuid().ToString, OracleType.VarChar)
        End If


        insertPara(10) = DatabaseHelper.NewParameter(":ToIssueERPMaterialID", para("ToIssueERPMaterialID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(insertSql, insertPara)
        If i > 0 Then
            InsertIssueErpMaterial = True
            Exit Function
        End If
        InsertIssueErpMaterial = False

    End Function
    ''' <summary>
    ''' 更新集件信息表按唯一ID
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateCollectErpMaterial(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.UpdateCollectErpMaterial
        Dim strSql As New StringBuilder
        strSql.AppendLine("UPDATE COLLECTERPMATERIAL CEM SET QTY=:QTY WHERE CEM.COLLECTERPMATERIALID=:COLLECTERPMATERIALID")

        Dim updatePara(1) As OracleParameter
        'modify tianFW 2017/10/10
        updatePara(0) = DatabaseHelper.NewParameter(":QTY", para("Qty"), OracleType.Number)
        updatePara(1) = DatabaseHelper.NewParameter(":COLLECTERPMATERIALID", para("CollectErpMaterialID"), OracleType.VarChar)
        '
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        If i >= 0 Then
            UpdateCollectErpMaterial = True
            Exit Function
        End If
        UpdateCollectErpMaterial = False
    End Function
    ''' <summary>
    ''' 更新装配信息信息表按唯一ID
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateIssueErpMaterial(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.UpdateIssueErpMaterial
        Dim strSql As New StringBuilder
        strSql.AppendLine("UPDATE ISSUEERPMATERIAL I SET ISSUEQTY=:ISSUEQTY,NOTE=:NOTE WHERE I.ISSUEERPMATERIALID=:ISSUEERPMATERIALID")

        Dim updatePara(2) As OracleParameter
        'modify tianFW 2017/10/10
        updatePara(0) = DatabaseHelper.NewParameter(":ISSUEQTY", para("IssueQty"), OracleType.Number)
        updatePara(1) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", para("IssueErpMaterialID"), OracleType.VarChar)
        updatePara(2) = DatabaseHelper.NewParameter(":NOTE", para("Note"), OracleType.VarChar)
        '
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        If i >= 0 Then
            UpdateIssueErpMaterial = True
            Exit Function
        End If
        UpdateIssueErpMaterial = False
    End Function   '还没用
    ''' <summary>
    ''' 按唯一ID删除装配信息表
    ''' </summary>
    ''' <param name="IssueERPMaterialID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function DelIssueErpMaterial(ByVal IssueERPMaterialID As String) As Boolean Implements uLMSIssueIBusiness.DelIssueErpMaterial
        Dim strSql As String = "DELETE ISSUEERPMATERIAL I WHERE I.ISSUEERPMATERIALID=:ISSUEERPMATERIALID"

        Dim delPara(0) As OracleParameter
        'modify tianFW 2017/10/10 
        delPara(0) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", IssueERPMaterialID, OracleType.VarChar)
        '
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, delPara)
        If i > 0 Then
            DelIssueErpMaterial = True
            Exit Function
        End If
        DelIssueErpMaterial = False
    End Function
    ''' <summary>
    ''' 插入数据到装配报废表,装配报废
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertIssueERPMaterialScrapInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.InsertIssueERPMaterialScrapInfo
        Dim strSql As String = "INSERT INTO ISSUEERPMATERIALSCRAPINFO(CHECKERID,CHECKNOTE,COLLECTERPMATERIALID,CREATEDATE, " &
                                  " ISSUEERPMATERIALID,ISSUEERPMATERIALSCRAPINFOID,LOSSQTY,LOSSREASONID,WORKERID)"

        strSql += "VALUES(:CHECKERID,:CHECKNOTE,:COLLECTERPMATERIALID,SYSDATE,:ISSUEERPMATERIALID,:ISSUEERPMATERIALSCRAPINFOID," &
                            ":LOSSQTY,:LOSSREASONID,:WORKERID)"
        Dim insertPara(7) As OracleParameter
        'modify tianFW 2017/10/10 
        insertPara(0) = DatabaseHelper.NewParameter(":CHECKERID", para("CheckerID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":CHECKNOTE", para("CheckNote"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":COLLECTERPMATERIALID", para("CollectErpMaterialID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", para("IssueErpMaterialID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALSCRAPINFOID", Guid.NewGuid().ToString, OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":LOSSQTY", para("LossQty"), OracleType.Number)
        insertPara(6) = DatabaseHelper.NewParameter(":LOSSREASONID", para("LossReasonID"), OracleType.VarChar)
        insertPara(7) = DatabaseHelper.NewParameter(":WORKERID", para("WorkerID"), OracleType.VarChar)
        '
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If i > 0 Then
            InsertIssueERPMaterialScrapInfo = True
            Exit Function
        End If
        InsertIssueERPMaterialScrapInfo = False
    End Function
    ''' <summary>
    ''' 获取装配报废表
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetIssueErpScrapInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSIssueIBusiness.GetIssueErpScrapInfo
        Dim strSql As New StringBuilder
        strSql.AppendLine("SELECT IEM.PRODUCTCHILDNO,LR.LOSSREASONNAME,E.FULLNAME DISMANTLENAME,IEMS.* FROM ISSUEERPMATERIALSCRAPINFO IEMS ")
        strSql.AppendLine(" LEFT JOIN ISSUEERPMATERIAL IEM ON IEM.ISSUEERPMATERIALID = IEMS.ISSUEERPMATERIALID")
        strSql.AppendLine(" LEFT JOIN LOSSREASON LR ON LR.LOSSREASONID=IEMS.LOSSREASONID")
        strSql.AppendLine(" LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = IEMS.DISMANTLEID") '拆卸人
        strSql.AppendFormat(" WHERE IEM.WORKFLOWSTEPID='{0}' AND IEM.CONTAINERID='{1}' ", para("WorkFlowStepID"), para("ContainerID"))
        If para.ContainsKey("IssueScrap") Then '装配报废
            strSql.AppendLine(" AND IEMS.CheckerID IS NOT NULL")
        ElseIf para.ContainsKey("IssueDismantle") Then '装配移除
            strSql.AppendLine(" AND IEMS.Dismantleid IS NOT NULL")
        End If
        Dim result = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
        GetIssueErpScrapInfo = result
    End Function

    ''' <summary>
    ''' 按唯一ID删除装配报废表
    ''' </summary>
    ''' <param name="issueERPMaterialScrapInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function DelIssueErpScrapInfo(ByVal issueERPMaterialScrapInfoID As String) As Boolean Implements uLMSIssueIBusiness.DelIssueErpScrapInfo
        Dim strSql As String = "DELETE ISSUEERPMATERIALSCRAPINFO IEMS WHERE IEMS.ISSUEERPMATERIALSCRAPINFOID =:ISSUEERPMATERIALSCRAPINFOID"
        Dim delPara(0) As OracleParameter
        'modify tianFW 2017/10/10
        delPara(0) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALSCRAPINFOID", issueERPMaterialScrapInfoID, OracleType.VarChar)
        '
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, delPara)
        If i > 0 Then
            DelIssueErpScrapInfo = True
            Exit Function
        End If
        DelIssueErpScrapInfo = False
    End Function
    ''' <summary>
    ''' 按唯一ID更新装配报废表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateIssueErpScrapInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.UpdateIssueErpScrapInfo
        Dim strSql As String = "UPDATE ISSUEERPMATERIALSCRAPINFO IEMS SET LOSSQTY = :LOSSQTY, IEMS.LOSSREASONID = :LOSSREASONID, " &
                           "IEMS.CHECKNOTE=:CHECKNOTE  WHERE IEMS.ISSUEERPMATERIALSCRAPINFOID = :ISSUEERPMATERIALSCRAPINFOID"
        Dim updatePara(3) As OracleParameter
        'modify tianFW 2017/10/10
        updatePara(0) = DatabaseHelper.NewParameter(":LOSSQTY", para("LossQty"), OracleType.VarChar)
        updatePara(1) = DatabaseHelper.NewParameter(":LOSSREASONID", para("LossReasonID"), OracleType.VarChar)
        updatePara(2) = DatabaseHelper.NewParameter(":CHECKNOTE", para("CheckNote"), OracleType.VarChar)
        updatePara(3) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALSCRAPINFOID", para("IssueErpMaterialScrapInfoID"), OracleType.VarChar)
        '
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql.ToString, updatePara)
        If i >= 0 Then
            UpdateIssueErpScrapInfo = True
            Exit Function
        End If
        UpdateIssueErpScrapInfo = False


    End Function
    ''' <summary>
    ''' 更新换件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function UpdateIssueChange(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.UpdateIssueChange
        Dim updateSql As String = " UPDATE ISSUEERPMATERIAL IEM  SET IEM.ISSUEQTY=:ISSUEQTY,IEM.TOISSUEERPMATERIALID=:TOISSUEERPMATERIALID WHERE IEM.ISSUEERPMATERIALID=:ISSUEERPMATERIALID"

        Dim updatePara(2) As OracleParameter
        updatePara(0) = DatabaseHelper.NewParameter(":ISSUEQTY", Convert.ToInt32(para("IssueQty")), OracleType.Number)
        updatePara(1) = DatabaseHelper.NewParameter(":TOISSUEERPMATERIALID", para("ToIssueERPMaterialID"), OracleType.VarChar)
        updatePara(2) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", para("IssueErpMaterialID"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(updateSql, updatePara)
        If i > 0 Then
            UpdateIssueChange = True
        Else
            UpdateIssueChange = False
        End If
    End Function

    ''' <summary>
    ''' 插入数据到装配报废表,集件移除
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function InsertIssueDismantleInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.InsertIssueDismantleInfo
        Dim strSql As String = "INSERT INTO ISSUEERPMATERIALSCRAPINFO(DISMANTLEID,CHECKNOTE,COLLECTERPMATERIALID,DISMANTLEDATE, " &
                                  " ISSUEERPMATERIALID,ISSUEERPMATERIALSCRAPINFOID,LOSSQTY,LOSSREASONID,WORKERID)"

        strSql += "VALUES(:DISMANTLEID,:CHECKNOTE,:COLLECTERPMATERIALID,SYSDATE,:ISSUEERPMATERIALID,:ISSUEERPMATERIALSCRAPINFOID," &
                            ":LOSSQTY,:LOSSREASONID,:WORKERID)"
        Dim insertPara(7) As OracleParameter

        insertPara(0) = DatabaseHelper.NewParameter(":DISMANTLEID", para("DismantleID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":CHECKNOTE", para("CheckNote"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":COLLECTERPMATERIALID", para("CollectErpMaterialID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALID", para("IssueErpMaterialID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":ISSUEERPMATERIALSCRAPINFOID", Guid.NewGuid().ToString, OracleType.VarChar)
        insertPara(5) = DatabaseHelper.NewParameter(":LOSSQTY", para("LossQty"), OracleType.Number)
        insertPara(6) = DatabaseHelper.NewParameter(":LOSSREASONID", para("LossReasonID"), OracleType.VarChar)
        insertPara(7) = DatabaseHelper.NewParameter(":WORKERID", para("WorkerID"), OracleType.VarChar)

        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If i > 0 Then
            InsertIssueDismantleInfo = True
            Exit Function
        End If
        InsertIssueDismantleInfo = False
    End Function

    ''' <summary>
    ''' 根据集件ID查询集件移除信息 create tianFW 2017/10/13
    ''' </summary>
    ''' <param name="IssueERPMaterialID">集件唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetIssueERPMaterialScrapInfoByID(ByVal IssueERPMaterialID As String) As DataTable Implements uLMSIssueIBusiness.GetIssueERPMaterialScrapInfoByID
        Dim strsql As String = "SELECT IIO.*,EM.FULLNAME CHECKERNAME FROM ISSUEERPMATERIALSCRAPINFO IIO "
        strsql += " LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = IIO.CHECKERID"
        strsql += String.Format(" WHERE IIO.CHECKERID IS NULL AND IIO.CREATEDATE IS NULL AND IIO.ISSUEERPMATERIALID IS NOT NULL AND IIO.ISSUEERPMATERIALID='{0}'", IssueERPMaterialID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 修改移除集件信息检验信息（即确认报废） create tianFW 2017/10/13
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdataIssueERPMaterialScrapInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSIssueIBusiness.UpdataIssueERPMaterialScrapInfo
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE ISSUEERPMATERIALSCRAPINFO IIO SET IIO.CHECKERID='{0}'", para("EmployeeID"))
        strsql.AppendFormat(",IIO.CREATEDATE=SYSDATE WHERE IIO.ISSUEERPMATERIALSCRAPINFOID='{0}'", para("IssueERPMaterialScrapID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 确认撤销集件移除 create tianFW 2017/10/16
    ''' </summary>
    ''' <param name="IssueERPMaterialScrapID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CancleScrap(ByVal IssueERPMaterialScrapID As String) As Boolean Implements uLMSIssueIBusiness.CancleScrap
        Dim strsql As String = String.Format(" UPDATE ISSUEERPMATERIALSCRAPINFO IIO SET IIO.CHECKERID=NULL,IIO.CREATEDATE=NULL WHERE IIO.ISSUEERPMATERIALSCRAPINFOID='{0}'", IssueERPMaterialScrapID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
End Class
